package com.pkuse.bjousystem.repository.student;

import com.pkuse.bjousystem.dto.student.XueShengLeiBieDTO;
import com.pkuse.bjousystem.entity.student.XueShengLeiBieDaiMa;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface XueShengLeiBieDaiMaRepository extends CrudRepository<XueShengLeiBieDaiMa, String> {

    @Query("select new com.pkuse.bjousystem.dto.student.XueShengLeiBieDTO(xslbdmb.xslbdm, xslbdmb.xslbmc) " +
            "from XueShengLeiBieDaiMa xslbdmb where xslbdmb.xslbdm in ( " +
            "select distinct bjxxb.xslbdm from XueShengBiYe xsbyb " +
            "inner join Student xsb on (xsbyb.xh=xsb.xh and xsbyb.bynd is not null and xsbyb.byxq is not null and (:xxdm is null or xsb.xxdm = :xxdm))" +
            "left join BanJiXinXi bjxxb on xsb.bdm=bjxxb.bdm)")
    List<XueShengLeiBieDTO> listXueShengLeiBieForGraduation(@Param("xxdm") String xxdm);

    @Query("select new com.pkuse.bjousystem.dto.student.XueShengLeiBieDTO(xslbdmb.xslbdm, xslbdmb.xslbmc) " +
            "from XueShengLeiBieDaiMa xslbdmb where xslbdmb.xslbdm in ( " +
            "select distinct bjxxb.xslbdm from XueShengXuanKeJieGuo xsxkjgb " +
            "inner join BanJiXinXi bjxxb on (xsxkjgb.bdm=bjxxb.bdm and xsxkjgb.pzh is not null and (:xxdm is null or xsxkjgb.xxdm = :xxdm)))")
    List<XueShengLeiBieDTO> listXueShengLeiBieForRegistry(@Param("xxdm") String xxdm);

    @Query("select new com.pkuse.bjousystem.dto.student.XueShengLeiBieDTO(xslbdmb.xslbdm, xslbdmb.xslbmc) " +
            "from XueShengLeiBieDaiMa xslbdmb where xslbdmb.xslbdm in ( " +
            "select distinct bjxxb.xslbdm from Student xsb " +
            "inner join BanJiXinXi bjxxb on (xsb.bdm=bjxxb.bdm and xsb.xjztdm='1' and (:xxdm is null or xsb.xxdm = :xxdm)))")
    List<XueShengLeiBieDTO> listXueShengLeiBieForEnrollment(@Param("xxdm") String xxdm);
}
